﻿using System;
using System.Collections.Generic;
using System.Text;
using ProcessControl.Interface;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Configuration;
using System.IO;
using ProcessControl.Entity;

namespace ProcessControl.Logic
{
    public class CapexLogic : ICapex
    {
        private DataHelper db = new DataHelper();
        public string flag = DataHelper.ParameterFlag;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="capexNumber"></param>
        /// <returns></returns>
        public List<Entity.CapexDetailEntity> GetCapexDetailByCapexNO(string capexNumber)
        {
            try
            {
                StringBuilder strsql = new StringBuilder();
                strsql.Append("select P.BUDGETNUMBER,P.DESCRIBE,P.BALANCE,P.PROJECTNAME,P.FORMID,P.ITEMNUMBER,P.PROJECTLEADERNAME from dbo.PROC_CAPEX  as C"
                   + " inner join dbo.PROC_CAPEX_DT as P on p.FORMID=C.FORMID "
                   + " where C.DOCUMENTNO='" + capexNumber.Trim() + "' and (P.status=0 or P.status=1)");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                db.AddInParameter(dbcom, flag + "DOCUMENTNO", DbType.String, capexNumber);
                DbDataReader dr = db.ExecuteReader(dbcom);
                List<CapexDetailEntity> list = new List<CapexDetailEntity>();
                while (dr.Read())
                {
                    CapexDetailEntity item = new CapexDetailEntity();
                    if (dr["FORMID"] != null && !String.IsNullOrEmpty(dr["FORMID"].ToString()))
                    {
                        item.FormID = dr["FORMID"].ToString();
                    }
                    if (dr["BUDGETNUMBER"] != null && !String.IsNullOrEmpty(dr["BUDGETNUMBER"].ToString()))
                    {
                        item.BudgetNumber = dr["BUDGETNUMBER"].ToString();
                    }
                    if (dr["DESCRIBE"] != null && !String.IsNullOrEmpty(dr["DESCRIBE"].ToString()))
                    {
                        item.Describe = dr["DESCRIBE"].ToString();
                    }
                    if (dr["BALANCE"] != null && !String.IsNullOrEmpty(dr["BALANCE"].ToString()))
                    {
                        item.Balance = double.Parse(dr["BALANCE"].ToString());
                    }
                    if (dr["PROJECTNAME"] != null && !String.IsNullOrEmpty(dr["PROJECTNAME"].ToString()))
                    {
                        item.ProjectName = dr["PROJECTNAME"].ToString();
                    }
                    if (dr["ITEMNUMBER"] != null && !String.IsNullOrEmpty(dr["ITEMNUMBER"].ToString()))
                    {
                        item.ItemNumber = dr["ITEMNUMBER"].ToString();
                    }
                    if (dr["PROJECTLEADERNAME"] != null && !String.IsNullOrEmpty(dr["PROJECTLEADERNAME"].ToString()))
                    {
                        item.ProjectLeaderName = dr["PROJECTLEADERNAME"].ToString();
                    }
                    list.Add(item);
                }
                dr.Close();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            //throw new NotImplementedException();
        }

        /// <summary>
        /// 获取Budget的余额
        /// </summary>
        /// <param name="BugetNo"></param>
        /// <returns></returns>
        public double GetBugetBalace(string BugetNo)
        {
            try
            {
                double strBugetBalace = 0;
                StringBuilder strsql = new StringBuilder();
                strsql.Append("select AMOUNT  from dbo.PROC_BUDGETSET where BUDGETCODE ='" + BugetNo.Trim() + "'");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                // DbDataReader dr = db.ExecuteReader(dbcom);

                DataTable dt = db.ExecuteDataTable(dbcom);
                if (dt.Rows.Count > 0)
                {
                    strBugetBalace = double.Parse(dt.Rows[0]["AMOUNT"].ToString());
                }
                return strBugetBalace;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            //throw new NotImplementedException();
        }
        
        /// <summary>
        /// 获取Capex的金额
        /// </summary>
        /// <param name="BugetNo"></param>
        /// <returns></returns>
        public double GetCapexAmout(string FormId, string BugetNo)
        {
            try
            {
                double strBugetBalace = 0;
                StringBuilder strsql = new StringBuilder();
                strsql.Append("select AMOUNT,USEDAMOUNT,AMOUNT-USEDAMOUNT as Balace from dbo.PROC_CAPEX_DT where BUDGETNUMBER ='" + BugetNo.Trim() + "' and FORMID='" + FormId.Trim() + "'");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                // DbDataReader dr = db.ExecuteReader(dbcom);
                DataTable dt = db.ExecuteDataTable(dbcom);
                if (dt.Rows.Count > 0)
                {
                    strBugetBalace = double.Parse(dt.Rows[0]["Balace"].ToString());   //Capex所剩金额
                }
                return strBugetBalace;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            //throw new NotImplementedException();
        }

        /// <summary>
        /// 获取Capex已使用的金额
        /// </summary>
        /// <param name="BugetNo"></param>
        /// <returns></returns>
        public double GetCapexUsedAmout(string FormId, string BugetNo)
        {
            try
            {
                double strBugetBalace = 0;
                StringBuilder strsql = new StringBuilder();
                strsql.Append("select USEDAMOUNT from dbo.PROC_CAPEX_DT where BUDGETNUMBER ='" + BugetNo.Trim() + "' and FORMID='" + FormId.Trim() + "'");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                // DbDataReader dr = db.ExecuteReader(dbcom);
                DataTable dt = db.ExecuteDataTable(dbcom);
                if (dt.Rows.Count > 0)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[0]["USEDAMOUNT"].ToString()))
                    {
                        strBugetBalace = double.Parse(dt.Rows[0]["USEDAMOUNT"].ToString());   //Capex金额
                    }
                }
                return strBugetBalace;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            //throw new NotImplementedException();
        }

        /// <summary>
        /// 更新Capex的行数据状态（0为初始值，1为已经提交审批通过的）
        /// </summary>
        /// <param name="BugetNumber"></param>
        /// <param name="FormID"></param>
        /// <returns></returns>
        public bool UpdateRowsStatus(string BugetNumber, string FormID,double CurrAmount)
        {
            bool IsSuccess = false;
            try
            {
                StringBuilder strsql = new StringBuilder();
                strsql.Append("update dbo.PROC_CAPEX_DT set STATUS=1, USEDAMOUNT='" + CurrAmount + "'  where FORMID='" + FormID.Trim() + "' and  BUDGETNUMBER='" + BugetNumber.Trim() + "'");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                DataTable dt = db.ExecuteDataTable(dbcom);
                IsSuccess = true;

            }
            catch (Exception ex)
            {
                string strMessage = ex.Message;
            }
            return IsSuccess;
        }

        /// <summary>
        /// 更新终止本次Capex的余额至预算表中
        /// </summary>
        /// <param name="CapexFormId"></param>
        /// <param name="CurrPRAmout"></param>
        /// <param name="CapexName"></param>
        /// <param name="BudgetNumber"></param>
        /// <returns></returns>
        public bool UpdateEndAmount(string CapexFormId, double CurrPRAmout, string CapexName, string BudgetNumber)
        {
            bool IsSuccess = false;
            try
            {
                StringBuilder strsql = new StringBuilder();
                strsql.Append("update  dbo.PROC_CAPEX_DT  set AMOUNT='" + CurrPRAmout + "' where FORMID='" + CapexFormId.Trim() + "' and PROJECTNAME='" + CapexName.Trim() + "' and BUDGETNUMBER='" + BudgetNumber.Trim() + "'");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                DataTable dt = db.ExecuteDataTable(dbcom);
                IsSuccess = true;
            }
            catch (Exception ex)
            {
                string strMessage = ex.Message;
            }
            return IsSuccess;
        }

        /// <summary>
        /// 更新Capex表的已使用金额
        /// </summary>
        /// <param name="BugetNumber"></param>
        /// <param name="FormID"></param>
        /// <returns></returns>
        public bool UpdateUsedAmount(string BugetNumber, string FormID, double CurrAmount)
        {
            bool IsSuccess = false;
            try
            {
                StringBuilder strsql = new StringBuilder();
                strsql.Append("update dbo.PROC_CAPEX_DT set USEDAMOUNT='" + CurrAmount + "' where FORMID='" + FormID.Trim() + "' and  BUDGETNUMBER='" + BugetNumber.Trim() + "'");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                DataTable dt = db.ExecuteDataTable(dbcom);
                IsSuccess = true;

            }
            catch (Exception ex)
            {
                string strMessage = ex.Message;
            }
            return IsSuccess;
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="capexNumber"></param>
        /// <returns></returns>
        public List<Entity.CapexDetailEntity> GetCapexDetailByCapexNO(string capexNumber,string BugetNumber)
        {
            try
            {
                StringBuilder strsql = new StringBuilder();
                strsql.Append("select P.BUDGETNUMBER,P.DESCRIBE,P.BALANCE,P.PROJECTNAME,P.FORMID,P.ITEMNUMBER,P.PROJECTLEADERNAME from dbo.PROC_CAPEX  as C"
                   + " inner join dbo.PROC_CAPEX_DT as P on p.FORMID=C.FORMID "
                   + " where C.DOCUMENTNO='" + capexNumber.Trim() + "' and (P.status=0 or P.status=1)  and BUDGETNUMBER='" + BugetNumber.Trim() + "' ");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                db.AddInParameter(dbcom, flag + "DOCUMENTNO", DbType.String, capexNumber);
                DbDataReader dr = db.ExecuteReader(dbcom);
                List<CapexDetailEntity> list = new List<CapexDetailEntity>();
                while (dr.Read())
                {
                    CapexDetailEntity item = new CapexDetailEntity();
                    if (dr["FORMID"] != null && !String.IsNullOrEmpty(dr["FORMID"].ToString()))
                    {
                        item.FormID = dr["FORMID"].ToString();
                    }
                    if (dr["BUDGETNUMBER"] != null && !String.IsNullOrEmpty(dr["BUDGETNUMBER"].ToString()))
                    {
                        item.BudgetNumber = dr["BUDGETNUMBER"].ToString();
                    }
                    if (dr["DESCRIBE"] != null && !String.IsNullOrEmpty(dr["DESCRIBE"].ToString()))
                    {
                        item.Describe = dr["DESCRIBE"].ToString();
                    }
                    if (dr["BALANCE"] != null && !String.IsNullOrEmpty(dr["BALANCE"].ToString()))
                    {
                        item.Balance = double.Parse(dr["BALANCE"].ToString());
                    }
                    if (dr["PROJECTNAME"] != null && !String.IsNullOrEmpty(dr["PROJECTNAME"].ToString()))
                    {
                        item.ProjectName = dr["PROJECTNAME"].ToString();
                    }
                    if (dr["ITEMNUMBER"] != null && !String.IsNullOrEmpty(dr["ITEMNUMBER"].ToString()))
                    {
                        item.ItemNumber = dr["ITEMNUMBER"].ToString();
                    }
                    if (dr["PROJECTLEADERNAME"] != null && !String.IsNullOrEmpty(dr["PROJECTLEADERNAME"].ToString()))
                    {
                        item.ProjectLeaderName = dr["PROJECTLEADERNAME"].ToString();
                    }
                    list.Add(item);
                }
                dr.Close();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            //throw new NotImplementedException();
        }

        /// <summary>
        /// 获取项目负责人
        /// </summary>
        /// <returns></returns>
        public DataTable Excel()
        {
            DataTable dt = null;
            try
            {
                StringBuilder strsql = new StringBuilder();
                strsql.Append("select U.CNNAME,U.LOGINNAME from dbo.V_DEPARTMENT as D "
                      + "  inner join COM_JOB as J on J.DEPARTMENTID=D.DEPARTMENTID "
                        + "  inner join COM_USER as U on U.USERID=J.USERID "
                      + "  where D.DEPARTMENTNAME='项目管理'");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                dt = db.ExecuteDataTable(dbcom);               
            }
            catch (Exception ex)
            {
                string strMess = ex.Message;
            }
            return dt;
        }


        /// <summary>
        /// 获取项目支出原因
        /// </summary>
        /// <returns></returns>
        public DataTable GetProjectReason()
        {
            DataTable dt = null;
            try
            {
                StringBuilder strsql = new StringBuilder();
                strsql.Append("select REASON from SYNC_ATTACHMENT_DT a  "
                      + "  where a.id = (select min(ID) from SYNC_ATTACHMENT_DT  "
                        + "  where REASON=a.REASON)order by ID ");
                DbCommand dbcom = db.GetSqlStringCommond(strsql.ToString());
                dt = db.ExecuteDataTable(dbcom);
            }
            catch (Exception ex)
            {
                string strMess = ex.Message;
            }
            return dt;
        }
    }
}
